from typing import Union, Optional

from pydantic import Field as PydanticField
from sqlalchemy import update, delete, Column, Integer, String
from config import create_db_and_tables, session
from sqlmodel import SQLModel, Field, select, or_


class Hero(SQLModel, table=True):
    __table_args__ = {'extend_existing': True}
    id: Union[int, None] = Field(default=None, primary_key=True)
    name: str = Field(index=True)  # 添加索引
    age: Union[int, None] = None

    def __init__(self, **data):
        super().__init__(**data)
        self._current = Field(default=None,sa_column=Column(Integer, default=1))

    @property
    def current(self):
        return self._current

    @current.setter
    def current(self, value):
        self._current = value


class Team(SQLModel, table=True):
    __table_args__ = {'extend_existing': True}
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str  # 设置当前页码，默认值为1
  # 设置每页大小，默认值为10
  # 设置排序器，默认值为None
create_db_and_tables()


def create_heroes():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
    hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
    hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
    hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
    hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
    hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
    print("\nAfter adding to the session")
    print("Hero 1:", hero_1)
    print("Hero 2:", hero_2)
    print("Hero 3:", hero_3, '\n')

    session.add(hero_1)
    session.add(hero_2)
    session.add(hero_3)
    session.add(hero_4)
    session.add(hero_5)
    session.add(hero_6)
    session.add(hero_7)
    session.commit()

    print("\n==========================\n"
          "提交后，访问对象：")
    print("Hero 1:", hero_1)
    print("Hero 2:", hero_2)
    print("Hero 3:", hero_3, '\n')

    # 访问属性，会触发隐式刷新
    print("\n==========================\n"
          "\n提交后，访问属性：")
    print("Hero 1:", hero_1.id)
    print("Hero 2:", hero_2.name)
    print("Hero 3:", hero_3.age, '\n')

    # 显示刷新
    session.refresh(hero_1)
    session.refresh(hero_2)
    session.refresh(hero_3)

    print("\n==========================\n"
          "提交后，在显示刷新后，访问对象：")
    print("Hero 1:", hero_1)
    print("Hero 2:", hero_2)
    print("Hero 3:", hero_3, '\n')

    session.close()


# 插入数据
# create_heroes()
def select_all_hero():
    statement = select(Hero)
    results = session.exec(statement)

    for hero in results:
        print('读取到的数据', hero)


# select_all_hero()


def select_hero_with_where():
    # statement = select(Hero).where(Hero.name == "Deadpond")
    # statement = select(Hero).where(Hero.name != "Deadpond")
    # statement = select(Hero).where(Hero.age > 35)
    # statement = select(Hero).where(Hero.age >= 35)
    # statement = select(Hero).where(Hero.age >= 35).where(Hero.age < 40)
    # statement = select(Hero).where(Hero.age >= 35, Hero.age < 40)
    # # OR 表达式
    # statement = select(Hero).where(or_(Hero.age <= 35, Hero.age > 90))
    # # AND 与 OR 表达式
    # statement = select(Hero).where(Hero.name == 'Black Lion', or_(Hero.age <= 35, Hero.age > 90))
    #
    # statement = select(Hero).where(or_(Hero.age <= 35, Hero.age > 90)).limit(1)
    #
    # statement = select(Hero).where(or_(Hero.age <= 35, Hero.age > 90)).limit(1).offset(1)
    statement = select(Hero.age, Hero.name).where(or_(Hero.age <= 35, Hero.age > 90)).order_by(Hero.age)
    results = session.exec(statement)
    # result = session.exec(statement).first()
    # result = session.exec(statement).one()
    for hero in results:
        print('读取到的数据', hero)


# select_hero_with_where()


def get_hero_with_where():
    hero = session.get(Hero, 1)
    print("Hero:", hero)


# get_hero_with_where()
def update_heroes():
    statement = select(Hero).where(Hero.name == "Spider-Boy")
    results = session.exec(statement)
    hero = results.one()
    print("Hero:", hero)

    update(Hero).where(Hero.id == hero.id)
    hero.age = 20
    session.add(hero)
    session.commit()
    session.refresh(hero)
    print("Updated hero:", hero)

# update_heroes()
def update_heroes_2():
    statement = update(Hero).values(age=200).where(Hero.id == 2)
    result = session.exec(statement)
    session.commit()
    print('执行结果 ： ', str(result))


# update_heroes_2()


def delete_heroes():
    statement = delete(Hero).where(Hero.id == 1)
    result = session.exec(statement)
    session.commit()
    print('执行结果 ： ', result)

# delete_heroes()
